# -*- codeing = utf-8 -*-
# @Time: 2021/11/29 21:50
# @Author: Foxhuty
# @File: HSC_01.py
# @Software: PyCharm
# @Based on python 3.9

import pandas as pd

filepath = r'D:\年级管理数据\高2021级\高一下\高一下期末考试成绩分析\十一中\高一年级期末考试总分成绩.xlsx'
df_arts_ = pd.read_excel(filepath, sheet_name='文科', dtype={'考号': str})
df_science_ = pd.read_excel(filepath, sheet_name='理科', dtype={'考号': str})


def get_student_number_class(df_data):
    student_number_class = df_data['班级'].value_counts()
    student_number_class.name = '参考人数'
    student_number_class['年级'] = student_number_class.sum()
    return student_number_class


def exam_room_info(df_arts, df_science):
    """
    计算生成文理科各考室学生名单
    :return:
    """
    df_arts.sort_values(by='总分', ascending=False, inplace=True)
    df_science.sort_values(by='总分', ascending=False, inplace=True)
    if len(df_arts) % 30 != 0:
        room_numbers = [f'文科第{str(i + 1)}考室' for i in
                        list(range(len(df_arts) // 30 + 1))]
    else:
        room_numbers = [f'文科第{str(i + 1)}考室' for i in list(range(len(df_arts) // 30))]
    if len(df_science) % 30 != 0:
        room_numbers_science = [f'理科第{str(i + 1)}考室' for i in
                                list(range(len(df_science) // 30 + 1))]
    else:
        room_numbers_science = [f'理科第{str(i + 1)}考室' for i in
                                list(range(len(df_science) // 30))]
    print(room_numbers)
    print(room_numbers_science)
    df_arts = df_arts.copy()
    df_science = df_science.copy()
    df_arts['考室号'] = None
    df_arts['座位号'] = None
    df_arts = df_arts.loc[:, ['班级', '姓名', '考号', '考室号', '座位号']]
    df_arts.reset_index(drop=True, inplace=True)

    df_science['考室号'] = None
    df_science['座位号'] = None
    df_science = df_science.loc[:, ['班级', '姓名', '考号', '考室号', '座位号']]
    df_science.reset_index(drop=True, inplace=True)

    df_room_students = []
    df_room_students_science = []
    for idx, room_number in enumerate(room_numbers):
        begin = idx * 30
        end = begin + 30
        df_room_student = df_arts.iloc[begin:end]
        df_room_students.append((idx, room_number, df_room_student))
        # print(df_room_students)
    writer = pd.ExcelWriter(r'D:\成绩统计结果\文理科考室学生名单.xlsx')
    for idx, room_number, df_room_student in df_room_students:
        for i in df_room_student.index:
            df_room_student['考室号'].at[i] = room_number
            df_room_student['座位号'].at[i] = i + 1 if i < 30 else i - idx * 30 + 1
        df_room_student.to_excel(writer, sheet_name=room_number, index=False)
    for idx, room_number in enumerate(room_numbers_science):
        begin = idx * 30
        end = begin + 30
        df_room_student_science = df_science.iloc[begin:end]
        df_room_students_science.append((idx, room_number, df_room_student_science))
    for idx, room_number, df_room_student_science in df_room_students_science:
        for i in df_room_student_science.index:
            df_room_student_science['考室号'].at[i] = room_number
            df_room_student_science['座位号'].at[i] = i + 1 if i < 30 else i - idx * 30 + 1
        df_room_student_science.to_excel(writer, sheet_name=room_number, index=False)
    df_arts_general = df_arts.sort_values(by=['班级', '考室号'], ascending=[True, True])
    df_science_general = df_science.sort_values(by=['班级', '考室号'], ascending=[True, True])
    df_arts_general.to_excel(writer, sheet_name='文科', index=False)
    df_science_general.to_excel(writer, sheet_name='理科', index=False)
    # 计算考生座签
    # df_seat_arts = df_arts
    # df_seat_science = df_science
    for i in df_arts.index:
        df_arts.loc[i + 0.5] = ['班级', '姓名', '考号', '考室号', '座位号']
        # df2.to_excel(f'{df2.iloc[0,0]}.xlsx',index=False)
    df_arts.sort_index(inplace=True, ignore_index=True)
    for i in df_science.index:
        df_science.loc[i + 0.5] = ['班级', '姓名', '考号', '考室号', '座位号']
    df_science.sort_index(inplace=True, ignore_index=True)
    df_arts.to_excel(writer, sheet_name='文科座签', index=False)
    df_science.to_excel(writer, sheet_name='理科座签', index=False)
    writer.close()
    print('successfully done')


if __name__ == '__main__':
    exam_room_info(df_arts_, df_science_)
